package www.test.utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;




public class jdbcUtils
{
	
	private static String driver = null;
	private static String url = null;
	private static String username =null;
	private static String password = null;
	static 
	{
		try
		{
			
			//读取db.properties文件中的数据库连接信息
			InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
			Properties prop = new Properties();
			prop.load(in);
			driver=prop.getProperty("oracleDb_Driver");
			url=prop.getProperty("oracleDb_Url");
			username=prop.getProperty("oracleDb_User");
			password=prop.getProperty("oracleDb_Password");
			/**
			 * 驱动加载
			 */
			Class.forName(driver);
			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		
	}
	/**
	 * 链接数据库
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException
	{
		return DriverManager.getConnection(url,username,password);
		
				
	}

	/**
	 * 释放链接
	 */
	public static void release(Connection conn,Statement st ,ResultSet rs )
	{
		if(rs!=null)
		{
			try
			{
				rs.close();
			}
			catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(st!=null)
		{
			try
			{
				st.close();
			}
			catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(conn!=null)
		{
			 try
			{
				conn.close();
			}
			catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 万能查询
	 * 不带分页
	 * @throws SQLException 
	 */
	public static List<HashMap<String, ?>> select(String sql ,Object params[]) throws SQLException 
	{
		Connection conn =null;
		PreparedStatement st =null;
		ResultSet rs = null;
		try
		{
			conn = getConnection();
			st =conn.prepareStatement(sql);
			for(int i = 0 ;i < params.length;i++)
			{
				st.setObject(i+1,params[i]);
				
			}
			rs=st.executeQuery();
			return  BeanListHandler.handler(rs);
		}
		finally
		{
			release(conn, st, rs);
		}
	}
	
	
	
	
	
	/**
	 * 带分页查询
	 * @throws SQLException 
	 */
	public static List<HashMap<String, ?>> select(String sql,Object params[], int pageSize,int currentPage) throws SQLException
	{
		Connection conn =null;
		PreparedStatement st =null;
		ResultSet rs = null;
		try
		{
			conn = getConnection();
			
			String sql0 ="   SELECT /*+ FIRST_ROWS */*FROM (SELECT MAX(ROWNUM) OVER() AS PAGE_ITEM_COUNT, ROWNUM PAGE_ROW_NUM, T.* FROM ("
			+sql+") T) T WHERE T.PAGE_ROW_NUM >= "+((currentPage-1)*pageSize+1)+" AND T.PAGE_ROW_NUM <= "+ currentPage*pageSize;
			
			System.out.println(sql0);
			st =conn.prepareStatement(sql0);
			for(int i = 0 ;i < params.length;i++)
			{
				st.setObject(i+1,params[i]);
				
			}
			rs=st.executeQuery();
			return  BeanListHandler.handler(rs);
		}
		finally
		{
			release(conn, st, rs);
		}
	}
	
	
	

	
	/**
	 * 万能更新
	 * @throws SQLException 
	 */
	public static boolean update(String sql , Object params[]) throws SQLException
	{
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs =null;
		try
		{
			conn = getConnection();
			st = conn.prepareStatement(sql);
			for(int i = 0 ;i <params.length; i ++)
			{
				st.setObject(i+1, params[i]);
			}
			int num =st.executeUpdate();
			if(num>0)
			{
				return true;
			}
			else
			{
				return false;
			}
			
		}
		finally
		{
			release(conn, st, rs);
		}
	}


}
